<?php
session_start(); // 启用会话
require_once BASE_PATH.'/db/database.php';

class Explore extends Database {

    // 记录已选择的标签 ID
    //private $selectedIds = [];
    private $maxRecords;

    public function __construct() {
        parent::__construct(); // 调用父类构造函数
        $this->maxRecords = $this->getTotalRecords(); // 动态获取最大记录数
        // 初始化选中的 ID，如果不存在则设置为空数组
        if (!isset($_SESSION['selectedIds'])) {
            $_SESSION['selectedIds'] = [];
        }
    }

    /*
     * choose = 1 随机12个Tags,  其他数字 固定前12个最多的tags
     *
     *不去重tags查询
    SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', numbers.n), ',', -1)) AS tag FROM `note` JOIN (SELECT n.n FROM (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) n) AS numbers ON CHAR_LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', numbers.n), ',', -1)) = 2 WHERE CHAR_LENGTH(tags) - CHAR_LENGTH(REPLACE(tags, ',', '')) + 1 >= numbers.n;

    *去重tags查询
    SELECT DISTINCT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', numbers.n), ',', -1)) AS tag FROM note JOIN (SELECT n.n FROM (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) n) AS numbers ON CHAR_LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', numbers.n), ',', -1)) = 2 WHERE CHAR_LENGTH(tags) - CHAR_LENGTH(REPLACE(tags, ',', '')) + 1 >= numbers.n;
     */
    public function randomTags($choose) {
        //不去重查询
        $sql = "SELECT TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', numbers.n), ',', -1)) AS tag FROM note JOIN (SELECT n.n FROM (SELECT 1 AS n UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) n) AS numbers ON CHAR_LENGTH(SUBSTRING_INDEX(SUBSTRING_INDEX(tags, ',', numbers.n), ',', -1)) = 2 WHERE CHAR_LENGTH(tags) - CHAR_LENGTH(REPLACE(tags, ',', '')) + 1 >= numbers.n;";
        $tags = $this->getAll($sql);
        $decodedTags = json_decode($tags,true);

        $tagCount = [];

        // 统计每个标签的出现次数
        foreach ($decodedTags as $entry) {
            $tag = $entry['tag'];
            if (isset($tagCount[$tag])) {
                $tagCount[$tag]++;
            } else {
                $tagCount[$tag] = 1;
            }
        }

        // 将标签和计数转换为数组
        $result = [];
        foreach ($tagCount as $tag => $count) {
            $result[] = ['tag' => $tag, 'count' => $count];
        }

        // 确定选择的数量
        $tagCount = count($result);
        $selectedCount = min(12, $tagCount); // 选择的标签数量最多为12

        if ($tagCount === 0) {
            return json_encode([]); // 如果没有标签，返回空数组的JSON
        }

        if ($choose == 1) {
            // 取重复率最多的前12个标签
            // 按照重复率（count）进行排序，从高到低
            usort($result, function($a, $b) {
                return $b['count'] <=> $a['count'];
            });
            $selectedTags = array_slice($result, 0, $selectedCount);
        } else {
            // 随机选择12个标签
            $randomKeys = array_rand($result, min(12, $tagCount)); // 随机选择的索引
            $selectedTags = [];
            foreach ($randomKeys as $index) {
                $selectedTags[] = $result[$index]; // 获取随机标签
            }
        }

        $json = json_encode($selectedTags);

        return $json;


    }


    // 获取随机的 note 内容
    public function randomNotes($num,$uid = null) {
        if ($uid){
            if (!isUid($uid)){
                return;
            }
        }
        $notes = [];

        // 从数据库获取所有有效的 id（is_view = 1 的笔记）
        $allidsql = "SELECT id FROM note WHERE is_view = 1";
        $allIds = array_column(json_decode($this->getAll($allidsql), true), 'id');
        // 如果没有剩余的 ID，则直接返回空
        if (empty($allIds)) {
            return json_encode($notes);
        }
        // 计算出剩余未选择的 ID
        //$remainingIds = array_diff(range(1, $this->maxRecords), $_SESSION['selectedIds']);
        $remainingIds = array_diff($allIds, $_SESSION['selectedIds']);

        // 如果没有剩余的 ID，则重置已选择的 ID
        if (empty($remainingIds)) {
            $_SESSION['selectedIds'] = []; // 重置
            $remainingIds = $allIds; // 重新生成所有 ID
        }

        // 随机选择 num 个 ID
        $randomKeys = array_rand($remainingIds, min($num, count($remainingIds)));

        foreach ($randomKeys as $key) {
            $id = $remainingIds[$key];
            $_SESSION['selectedIds'][] = $id; // 记录已选择的 ID

            // 查询 note 内容（假设 note 表有 note_id, type 和 title 字段）
            $note = json_decode($this->getNoteById($id), true);
            if ($note) {
                $statistics = json_decode($this->getNoteStatistics($note['note_id']), true); // 获取统计
                // 处理 image 字段
                $image = getFirstValidImage($note['image']);
                // 获取用户信息
                $user = json_decode($this->getUserInfo($note['user_id']), true);
                // 处理用户头像
                $avatar = !empty($user['avatar']) ? $user['avatar'] : '/data/avatar/default.jpg'; // 如果为空则设置为默认头像

                // 组合结果
                $notes[] = [
                    'note_id' => $note['note_id'],
                    'type' => $note['note_type'],
                    'title' => $note['title'],
                    'image' => $image,
                    'statistics' => $statistics,
                    'islike' => $this->getNoteIsLike($note['note_id'], $uid),
                    'user' => [
                        'user_id' => $user['user_id'],
                        'avatar' => $avatar,
                        'nickname' => $user['nickname']
                    ]
                ];
            }
        }

        return json_encode($notes, JSON_UNESCAPED_UNICODE);
    }

    public function getAllNotes($page = 1, $limit = 12) {
        $offset = ($page - 1) * $limit;

        // 获取满足条件的评论的总数
        $countSql = "SELECT COUNT(*) as total FROM note";

        $total = json_decode($this->getRow($countSql),true)['total'];

        // 计算总页数
        $totalPages = ceil($total / $limit);

        $sql = "SELECT n.*, 
                   u.avatar, u.nickname , ns.likes_count, ns.favorites_count, ns.comments_count
            FROM note n 
            JOIN user u ON n.user_id = u.user_id 
            JOIN note_statistics ns ON n.note_id = ns.note_id 
            ORDER BY n.note_time DESC 
            LIMIT $limit OFFSET $offset";

        $notesData = json_decode($this->getAll($sql), true);


        // 返回结果，包括评论数据、总页数和总记录数
        return [
            'totalPages' => $totalPages,
            'totalCount' => $total,
            'note' => $notesData,
        ];
    }

    /**
     * 根据用户的行为记录个性化推荐笔记获得ID
     */
    public function personalizedRandomNotes($num, $uid) {
        if (!isUid($uid)) {
            return json_encode(['status'=>201,'message'=>'uid error']);
        }


        $maxKeywordsPerBatch = 10;   // 定义每次处理的最大关键词数
        $notes = [];
        $batchSize = 1000;
        $offset = 0;

        // 从数据库获取所有有效的 id（is_view = 1 的笔记）
        $allidsql = "SELECT id FROM note WHERE is_view = 1";
        $allIds = array_column(json_decode($this->getAll($allidsql), true), 'id');
        //return json_encode($allIds);  num:3178
        // 如果没有剩余的 ID，则直接返回空
        if (empty($allIds)) {
            return json_encode($notes);
        }

        //sql获得查询关键词和浏览笔记历史
        $userActionsSql = "
        SELECT note_id,search_keywords 
        FROM user_actions
        WHERE user_id = '" . $uid . "' AND (action_type = 2 OR action_type = 3) 
        ORDER BY action_time DESC 
        LIMIT {$batchSize}    
    ";


        $userActions = json_decode($this->getAll($userActionsSql), true);

        $userKeywords = array_column($userActions, 'search_keywords');
        $noteIds = array_column($userActions, 'note_id');
        //return json_encode($noteIds);
        // 根据 note_id 获取对应的 keywords
        if (!empty($noteIds)) {
            $noteIdsStr = implode("','", $noteIds);
            $noteKeywordsSql = "
            SELECT tags 
            FROM note 
            WHERE note_id IN ('" . $noteIdsStr . "')
        ";

            $noteKeywords = json_decode($this->getAll($noteKeywordsSql), true);
            $noteKeywordsArray = array_column($noteKeywords, 'tags');
            //return json_encode($noteKeywordsArray);

            // 将获取到的 keywords 添加到用户关键词列表中
            foreach ($noteKeywordsArray as $keywords) {
                $userKeywords[] = $keywords;
            }
        }



        /*if (empty($userKeywords)) {
            return $this->randomNotes($num, $uid);
        }*/

        $userKeywords = array_filter($userKeywords);    //过滤空值
        $keywordFilter = implode(',', $userKeywords);       //整合完毕的关键词数组

        // 去重+排序
        $keywordsArray = explode(',', $keywordFilter);
        $keywordCounts = array_count_values($keywordsArray);
        arsort($keywordCounts);
        $sortedKeywords = array_keys($keywordCounts);
        //$keywordFilter = implode(',', $sortedKeywords);

        // 分批次查询关键词
        $chunks = array_chunk($sortedKeywords, $maxKeywordsPerBatch);       //关键词整理完成

        $selectedIds = $_SESSION['selectedIds'] ?? [];
        $notesCollected = [];

        // 存储生成的 SQL 语句
        $sqlQueries = [];

        // 生成所有 SQL 语句
        foreach ($chunks as $index => $keywordChunk) {
            // 构建 LIKE 查询条件
            $conditions = [];
            foreach ($keywordChunk as $keyword) {
                $conditions[] = "(tags LIKE '%" . $keyword . "%' OR content LIKE '%" . $keyword . "%' OR title LIKE '%" . $keyword . "%')";
            }
            $likeConditions = implode(' OR ', $conditions);

            // 构建查询笔记的 SQL
            $sql = "
                SELECT id 
                FROM note 
                WHERE is_view = 1 
                AND user_id != '" . $uid . "' 
                AND ($likeConditions) 
                LIMIT {$batchSize} OFFSET {$offset}
            ";

            // 将每个 SQL 语句以键值对的形式存储在数组中
            $sqlQueries[$index] = $sql;
        }
        //生成完毕sql
        //return json_encode($sqlQueries, JSON_UNESCAPED_UNICODE);

        // 从会话中获取上次执行的索引位置      开始查询
        if (!isset($_SESSION['currentQueryIndex'])) {
            $_SESSION['currentQueryIndex'] = 0;
        }

        // 当前的SQL执行起点和终点
        $batchQueryLimit = 5;  // 每次执行5个SQL查询
        $currentIndex = $_SESSION['currentQueryIndex'];
        $endIndex = $currentIndex + $batchQueryLimit;

        // 遍历执行当前批次的SQL
        $noteIds = [];
        for ($i = $currentIndex; $i < $endIndex; $i++) {
            $queryIndex = $i % count($sqlQueries);  // 防止索引越界，从头循环
            $query = $sqlQueries[$queryIndex];

            // 执行查询
            $result = json_decode($this->getAll($query), true);  // 假设 getAll 返回的 JSON 数据
            if (!empty($result)) {
                $ids = array_column($result, 'id');
                $noteIds = array_merge($noteIds, $ids);  // 合并所有查询到的笔记ID
            }
        }

        // 更新会话中记录的执行索引
        $_SESSION['currentQueryIndex'] = $endIndex % count($sqlQueries);  // 防止越界，重新从0开始


        //$noteIds = array_unique($noteIds);
        //return json_encode($_SESSION['currentQueryIndex']);
        $noteIds = array_unique($noteIds);      //去重
        //$noteIds = array_slice($noteIds, 0, $num);  // 限制返回数量
        return json_encode($noteIds, JSON_UNESCAPED_UNICODE);


        //return json_encode($notes, JSON_UNESCAPED_UNICODE);
    }

    //算法时间约等于230ms 3000 note时
    public function recommendNotes($num, $uid) {
        // 获取所有可推荐的ID
        $allidsql = "SELECT id FROM note WHERE is_view = 1 AND user_id != '" . $uid . "'";
        $allIds = array_column(json_decode($this->getAll($allidsql), true), 'id');

        // 获取推荐的ID
        $noteIds = json_decode($this->personalizedRandomNotes($num,$uid),true); //方法是生成推荐ID的逻辑，返回 array_unique($noteIds)
        //return json_encode($noteIds, JSON_UNESCAPED_UNICODE);
        if (!$noteIds){
            return $this->randomNotes($num,$uid);
        }

        if (!isset($_SESSION['recommendedIds'])) {
            $_SESSION['recommendedIds'] = [];
        }
        $recommendedIds = $_SESSION['recommendedIds'];

        // 随机打乱推荐的ID
        shuffle($noteIds);
        $noteIds = array_values($noteIds);  // 重置索引

        // 记录新的推荐的note_id，遍历 $noteIds，将未推荐过的添加到 $newRecommendations 中，已推荐的从 $noteIds 中移除。
        $newRecommendations = [];
        foreach ($noteIds as $noteId) {
            if (!in_array($noteId, $recommendedIds) && count($newRecommendations) < $num) {
                $newRecommendations[] = $noteId;  // 添加新的推荐ID
            } else {
                // 如果已经存在于推荐ID中，则移除并获取新的ID
                $noteIds = array_diff($noteIds, [$noteId]);  // 删除已存在的ID
            }
        }

        // 如果还没有达到推荐数量，继续从剩余的ID中随机抽取
        while (count($newRecommendations) < $num && !empty($noteIds)) {
            $newId = array_rand(array_flip($noteIds)); // 随机选择一个ID
            if (!in_array($newId, $recommendedIds)) {
                $newRecommendations[] = $newId;
                $noteIds = array_diff($noteIds, [$newId]); // 删除已选择的ID
            }
        }

        // 如果推荐数量还没达到 num
        if (count($newRecommendations) < $num) {
            // 从所有ID中排除已推荐的ID
            $remainingIds = array_diff($allIds, $recommendedIds, $newRecommendations);

            // 如果剩余ID还不足数量，则从剩余ID中随机选择补充
            if (!empty($remainingIds)) {
                $remainingNeeded = $num - count($newRecommendations);
                $randomAdditionalIds = array_rand(array_flip($remainingIds), min($remainingNeeded, count($remainingIds)));

                // 确保 $randomAdditionalIds 是数组
                if (is_array($randomAdditionalIds)) {
                    $newRecommendations = array_merge($newRecommendations, $randomAdditionalIds);
                } else {
                    $newRecommendations[] = $randomAdditionalIds; // 如果是单个值，直接加入数组
                }
            }
        }

        $newRecommendations = array_slice($newRecommendations, 0, $num);

        $_SESSION['recommendedIds'] = array_merge($recommendedIds, $newRecommendations); // 记录推荐过的数组

        // 如果推荐过的ID数量与所有笔记的ID数量相等，则清空会话
        if (count($_SESSION['recommendedIds']) >= count($allIds)) {
            $_SESSION['recommendedIds'] = []; // 清空已推荐ID
        }

        //return json_encode($_SESSION['recommendedIds']);


        //$newRecommendations // 返回新的推荐ID
        //return json_encode($newRecommendations);
        // 处理推荐的笔记信息
        $notes = [];
        foreach ($newRecommendations as $id) {
            // 查询笔记内容
            $note = json_decode($this->getNoteById($id), true);
            if ($note) {
                $statistics = json_decode($this->getNoteStatistics($note['note_id']), true);

                $image = getFirstValidImage($note['image']);
                $user = json_decode($this->getUserInfo($note['user_id']), true);
                $avatar = !empty($user['avatar']) ? $user['avatar'] : '/data/avatar/default.jpg';

                $notes[] = [
                    'note_id' => $note['note_id'],
                    'type' => $note['note_type'],
                    'title' => $note['title'],
                    'image' => $image,
                    'statistics' => $statistics,
                    'islike' => $this->getNoteIsLike($note['note_id'], $uid),
                    'user' => [
                        'user_id' => $user['user_id'],
                        'avatar' => $avatar,
                        'nickname' => $user['nickname']
                    ]
                ];
            }
        }

        return json_encode($notes, JSON_UNESCAPED_UNICODE);

        //return json_encode($newRecommendations, JSON_UNESCAPED_UNICODE);  // 返回新推荐的ID
    }




    public function profileNotes($uid, $pageSize, $page) {
        $notes = [];
        if (!isUid($uid)) {
            return;
        }
        // 计算 OFFSET
        $offset = ($page - 1) * $pageSize;

        // 查询用户笔记
        $sql = "SELECT n.image, n.note_id, n.note_time, n.note_type, n.title, n.check_result, n.user_id, u.avatar, u.nickname FROM note n JOIN user u ON n.user_id = u.user_id WHERE n.user_id = '{$uid}' AND n.is_view = 1 order by n.note_time desc LIMIT $pageSize OFFSET $offset";


        // 获取数据
        $currentNotes = json_decode($this->getAll($sql),true);
        if (empty($currentNotes)) {
            return null;
        }

        foreach ($currentNotes as $note) {
            // 获取统计信息
            $statistics = json_decode($this->getNoteStatistics($note['note_id']), true); // 假设有这个函数可以获取统计

            // 组合结果
            $notes[] = [
                'image' => getFirstValidImage($note['image']),
                'note_id' => $note['note_id'],
                'note_time' => $note['note_time'],
                'check_result' => $note['check_result'],
                'statistics' => $statistics,
                'title' => $note['title'],
                'type' => $note['note_type'],
                'islike' => $this->getNoteIsLike($note['note_id'],$uid),
                'user' => [
                    'user_id' => $note['user_id'],
                    'avatar' => $note['avatar'],
                    'nickname' => $note['nickname']
                ]
            ];
        }

        return json_encode($notes, JSON_UNESCAPED_UNICODE);
    }


    public function getTotalPageProfileNotes($uid, $pageSize) {

        if (!isUid($uid)) {
            return;
        }
        // 查询用户笔记
        $countSql = "SELECT COUNT(*) as total FROM note n JOIN user u ON n.user_id = u.user_id WHERE n.user_id = '{$uid}' AND n.is_view = 1";


        $countResult = json_decode($this->getRow($countSql),true);
        $totalCount = $countResult ? $countResult['total'] : 0; // 获取总记录数
        return ceil($totalCount / $pageSize); // 计算总页码
    }

    public function hideNote($uid, $nid)
    {
        if (!isUid($uid)||!isUid($nid)) {
            return json_encode(['status' => '201', 'message' => '无效uid']);
        }

        $sql = "UPDATE `note`
                SET `is_view` = 2
                WHERE `user_id` = '{$uid}' 
                AND `note_id` = '{$nid}'";

        $check_delsql = "DELETE FROM `check_note`
                WHERE `note_id` = '{$nid}'";

        $this->query($check_delsql);

        $res = $this->query($sql);
        return $res;
    }

    public function recoverNote($nid)
    {
        if (!isUid($nid)) {
            return json_encode(['status' => '201', 'message' => '无效nid']);
        }

        $sql = "UPDATE `note`
                SET `is_view` = 1
                WHERE `note_id` = '{$nid}'";

        $res = $this->query($sql);
        return $res;
    }

    public function upNoteCheckResult($nid,$status)
    {
        if (!isUid($nid)) {
            return json_encode(['status' => '201', 'message' => '无效nid']);
        }
        if($status!=1&&$status!=2){
            return json_encode(['status' => '201', 'message' => '无效status']);
        }

        $sql = "UPDATE `note`
                SET `check_result` = {$status}
                WHERE `note_id` = '{$nid}'";

        $this->query($sql);
    }

    public function deleteNote($nid)
    {
        if (!isUid($nid)) {
            return json_encode(['status' => '201', 'message' => '无效nid']);
        }

        $sql1 = "DELETE FROM `check_note`
                WHERE `note_id` = '{$nid}'";

        $sql2 = "DELETE FROM `comments`
                WHERE `note_id` = '{$nid}'";

        $sql3 = "DELETE FROM `note`
                WHERE `note_id` = '{$nid}'";

        $sql4 = "DELETE FROM `note_statistics`
                WHERE `note_id` = '{$nid}'";

        $sql5 = "DELETE FROM `user_actions`
                WHERE `note_id` = '{$nid}'";

        $sql6 = "DELETE FROM `video_note`
                WHERE `note_id` = '{$nid}'";

        $this->query($sql1);
        $this->query($sql2);
        $this->query($sql3);
        $this->query($sql4);
        $this->query($sql5);
        $this->query($sql6);
    }

    public function searchUserNotes($note_type,$uid, $keyword, $pageSize = 12, $page = 1) {
        $notes = [];

        if (empty($keyword)) {
            return json_encode(array());
        }
        // 过滤关键词，移除多余的空格和特殊字符
        $keyword = trim($keyword); // 去除前后空格
        $keyword = htmlspecialchars($keyword, ENT_QUOTES, 'UTF-8'); // 转义特殊字符

        // 计算 OFFSET
        $offset = ($page - 1) * $pageSize;

        // 查询笔记
        $sql = "SELECT n.image, n.note_id, n.note_time, n.note_type, n.title, n.user_id, u.avatar, u.nickname 
            FROM note n 
            JOIN user u ON n.user_id = u.user_id 
            WHERE n.is_view = 1 
              AND n.user_id = '{$uid}'
              AND (n.title LIKE '%{$keyword}%' OR n.content LIKE '%{$keyword}%') ";
        //return $sql;
        // 如果传入有效的 note_type，则添加到 SQL 查询中
        /*if (isset($note_type)) {
            if(in_array($note_type, [0, 1])){
                $sql .= " AND n.note_type = '{$note_type}' ";
            }
        }*/

        // 添加排序和分页
        $sql .= " ORDER BY n.note_time DESC 
              LIMIT {$pageSize} OFFSET {$offset}";


        // 获取数据
        $currentNotes = json_decode($this->getAll($sql), true);
        if (empty($currentNotes)) {
            return json_encode(array());
        }

        foreach ($currentNotes as $note) {
            // 获取统计信息
            $statistics = json_decode($this->getNoteStatistics($note['note_id']), true);

            $notes[] = [
                'image' => getFirstValidImage($note['image']),
                'note_id' => $note['note_id'],
                'note_time' => $note['note_time'],
                'statistics' => $statistics,
                'title' => $note['title'],
                'type' => $note['note_type'],
                'islike' => $this->getNoteIsLike($note['note_id'],$uid),
                'user' => [
                    'user_id' => $note['user_id'],
                    'avatar' => $note['avatar'],
                    'nickname' => $note['nickname']
                ]
            ];
        }

        return json_encode($notes, JSON_UNESCAPED_UNICODE);
    }

    public function searchNotes($note_type, $keyword, $pageSize = 12, $page = 1) {
        $notes = [];

        if (empty($keyword)) {
            return json_encode(array());
        }
        // 过滤关键词，移除多余的空格和特殊字符
        $keyword = trim($keyword); // 去除前后空格
        $keyword = htmlspecialchars($keyword, ENT_QUOTES, 'UTF-8'); // 转义特殊字符

        // 计算 OFFSET
        $offset = ($page - 1) * $pageSize;

        // 查询笔记
        $sql = "SELECT n.image, n.note_id, n.note_type, n.title, n.user_id, u.avatar, u.nickname 
            FROM note n 
            JOIN user u ON n.user_id = u.user_id 
            WHERE n.is_view = 1 
              AND (n.title LIKE '%{$keyword}%' OR n.content LIKE '%{$keyword}%') ";

        // 如果传入有效的 note_type，则添加到 SQL 查询中
        if (isset($note_type)) {
            if(in_array($note_type, [0, 1])){
                $sql .= " AND n.note_type = '{$note_type}' ";
            }
        }

        // 添加排序和分页
        $sql .= " ORDER BY n.note_id DESC 
              LIMIT {$pageSize} OFFSET {$offset}";


        // 获取数据
        $currentNotes = json_decode($this->getAll($sql), true);
        if (empty($currentNotes)) {
            return json_encode(array());
        }

        foreach ($currentNotes as $note) {
            // 获取统计信息
            $statistics = json_decode($this->getNoteStatistics($note['note_id']), true);

            $notes[] = [
                'image' => getFirstValidImage($note['image']),
                'note_id' => $note['note_id'],
                'statistics' => $statistics,
                'title' => $note['title'],
                'type' => $note['note_type'],
                'islike' => $this->getNoteIsLike($note['note_id'],$uid),
                'user' => [
                    'user_id' => $note['user_id'],
                    'avatar' => $note['avatar'],
                    'nickname' => $note['nickname']
                ]
            ];
        }

        return json_encode($notes, JSON_UNESCAPED_UNICODE);
    }


    public function likeNotes($uid, $pageSize, $page, $type) {
        $notes = [];
        if ($type != 0 && $type != 1) {         //0:点赞、1：收藏
            return;
        }

        if (!isUid($uid)) {
            return;
        }

        $offset = ($page - 1) * $pageSize;

        $sql = "SELECT note_id FROM user_actions WHERE user_id = '{$uid}' AND action_type = $type ORDER BY action_time DESC LIMIT $pageSize OFFSET $offset";
        $likedNotes = $this->getAll($sql);
        $likedNoteIds = array_column(json_decode($likedNotes, true), 'note_id');
        // 用户没赞过笔记，结束
        if (empty($likedNoteIds)) {
            return json_encode(array());
        }


        $likedNoteIds = array_map(function($id) {
            return "'$id'";
        }, $likedNoteIds);

        $noteIdsStr = implode(',', $likedNoteIds); // 连接成一个字符串
        $sql = "SELECT n.image, n.note_id, n.note_type, n.note_time, n.check_result, n.title, n.user_id, u.avatar, u.nickname 
        FROM note n 
        JOIN user u ON n.user_id = u.user_id 
        WHERE n.note_id IN ($noteIdsStr)
        ORDER BY FIELD(n.note_id, $noteIdsStr)";


        $currentNotes = json_decode($this->getAll($sql), true);

        if (empty($currentNotes)) {
            return null;
        }


        foreach ($currentNotes as $note) {

            $statistics = json_decode($this->getNoteStatistics($note['note_id']), true);


            $notes[] = [
                'image' => getFirstValidImage($note['image']),
                'note_id' => $note['note_id'],
                'note_time' => $note['note_time'],
                'check_result' => $note['check_result'],
                'statistics' => $statistics,
                'title' => $note['title'],
                'type' => $note['note_type'],
                'islike' => $this->getNoteIsLike($note['note_id'], $uid),
                'user' => [
                    'user_id' => $note['user_id'],
                    'avatar' => $note['avatar'],
                    'nickname' => $note['nickname']
                ]
            ];
        }


        return json_encode($notes, JSON_UNESCAPED_UNICODE);
    }
    public function getTotalPagelikeNotes($uid, $pageSize, $type) {

        if ($type != 0 && $type != 1) {         //0:点赞、1：收藏
            return;
        }

        if (!isUid($uid)) {
            return;
        }

        $countSql = "SELECT COUNT(*) as total FROM user_actions WHERE user_id = '{$uid}' AND action_type = $type";
        $countResult = json_decode($this->getRow($countSql),true);
        $totalCount = $countResult ? $countResult['total'] : 0; // 获取总记录数
        return ceil($totalCount / $pageSize); // 计算总页码

    }


    public function getCheckNotes($uid) {
        if (!isUid($uid)) {
            return json_encode(['status'=>201,'message'=>'uid error']);
        }

        // 获取 30 条随机的 note_id
        $query = "SELECT note_id FROM check_note WHERE check_type = 1 ORDER BY RAND() LIMIT 30";
        $note_ids = json_decode($this->getAll($query), true);

        if (empty($note_ids)) {
            return json_encode([]);
        }

        // 将 note_id 转换为字符串格式以供查询
        $ids = implode(',', array_map(function($id) {
            return "'".$id['note_id']."'";
        }, $note_ids));
        //return $ids;
        // 查询 note 表获取对应的 title、content 和 user_id
        $query = "SELECT note_id, note_type, title, content, image, tags, user_id FROM note WHERE note_id IN ($ids) AND user_id != '{$uid}'";

        $result = $this->getAll($query);

        return $result;


    }


    public function postNote($uid,$title,$content,$image,$tags) {

        if (!isUid($uid)) {
            return json_encode(['status'=>201,'message'=>'uid error']);
        }

        if (!$title||!$content||!$tags||!$image) {
            return json_encode(['status'=>201,'message'=>'所有内容都必须存在，输入框请填写完整！']);
        }

        $title = daddslashes($title);
        $content = daddslashes($content);
        $image = daddslashes($image);
        $tags = daddslashes($tags);

        $title = htmlspecialchars($title, ENT_QUOTES, 'UTF-8'); // 转义特殊字符

        // 生成唯一的 note_id
        $note_id = generateNoteId($uid);
        $note_type = (strpos($image, '.mp4') !== false) ? 1 : 0;
        $note_time = time();
        $publish_ip = getUserIpAsLong();
        $is_view = 1; // 默认设置为全部可见
        $check_result = 0; // 默认设置为审核中

        //处理文章末尾插入tags
        /*$tagArray = explode(',', $tags); // 按逗号分隔
        $tagArray = array_map('trim', $tagArray); // 去掉每个标签的空格
        $tagArray = array_filter($tagArray); // 去掉空的标签
        $formattedTags = array_map(function($tag) {
            return '#' . $tag; // 每个标签前加上 #
        }, $tagArray);
        $formattedTagsString = implode(' ', $formattedTags); // 以空格连接标签

        // 将 formattedTagsString 附加到 content
        $content .= ' ' . $formattedTagsString;

        preg_match_all('/#\p{L}+\b/u', $content, $matches);

        // 去重并保留原来的顺序
        $unique_tags = array_unique($matches[0]);

        // 将原来的标签和后面跟随的空格一起移除
        $content = preg_replace('/#\p{L}+\s?/u', '', $content);

        // 将去重后的标签添加回去
        $content .= ' ' . implode(' ', $unique_tags);*/

        // 进行 htmlspecialchars 转义
        $content = htmlspecialchars($content, ENT_QUOTES, 'UTF-8');
        $content = nl2br($content);

        // 创建 SQL 插入语句
        $sql = "INSERT INTO note (note_id, note_type, title, content, image, tags, note_time, publish_ip, user_id, is_view, check_result) 
            VALUES ('$note_id', '$note_type', '$title', '$content', '$image', '$tags', '$note_time', '$publish_ip', '$uid', '$is_view', '$check_result')";

        //return $sql;
        // 执行 SQL 语句
        if ($this->query($sql)) {
            $check_sql = "INSERT INTO `check_note` (`id`, `note_id`, `check_user_id`, `check_type`) VALUES (NULL, '$note_id', NULL, '1');";
            $this->query($check_sql);
            if($note_type == 1) {
                $videopic_sql = "INSERT INTO `video_note` (`id`, `note_id`, `video_src`, `is_ok`) VALUES (NULL, '$note_id', '$image', '0');";
                $this->query($videopic_sql);
            }
            return json_encode(['status'=>200,'message'=>'发布成功！耐心等待笔记审核！']);
        } else {
            return json_encode(['status'=>201,'message'=>'发布error']);
        }

    }

    public function changeNote($nid,$title,$content,$image,$tags) {

        if (!isUid($nid)) {
            return json_encode(['status'=>201,'message'=>'uid error']);
        }

        if (!$title||!$content||!$tags||!$image) {
            return json_encode(['status'=>201,'message'=>'所有内容都必须存在，输入框请填写完整！']);
        }

        $title = daddslashes($title);
        $content = daddslashes($content);
        $image = daddslashes($image);
        $tags = daddslashes($tags);

        $title = htmlspecialchars($title, ENT_QUOTES, 'UTF-8'); // 转义特殊字符
        //$content = htmlspecialchars($content, ENT_QUOTES, 'UTF-8'); // 转义特殊字符

        // 判断是否包含图片格式后缀（jpg、png 等）和视频格式（mp4）
        /*$is_video = (strpos($image, '.mp4') !== false);
        $is_image = (preg_match('/\.(jpg|jpeg|png|gif|bmp|webp|tiff|svg)$/i', $image));*/
        $files = explode(',', $image);

        $is_video = false;
        $is_image = false;

        foreach ($files as $file) {
            if (strpos($file, '.mp4') !== false) {
                $is_video = true;
            }
            if (preg_match('/\.(jpg|jpeg|png|gif|bmp|webp|tiff|svg)$/i', $file)) {
                $is_image = true;
            }
        }




        $note_type = (strpos($image, '.mp4') !== false) ? 1 : 0;
        $publish_ip = getUserIpAsLong();
        $check_result = 0; // 默认设置为审核中

        // 处理 tags 并附加到 content
        /*$tagArray = explode(',', $tags); // 按逗号分隔
        $tagArray = array_map('trim', $tagArray); // 去掉每个标签的空格
        $tagArray = array_filter($tagArray); // 去掉空的标签
        $formattedTags = array_map(function($tag) {
            return '#' . $tag; // 每个标签前加上 #
        }, $tagArray);
        $formattedTagsString = implode(' ', $formattedTags); // 以空格连接标签

        // 将 formattedTagsString 附加到 content
        $content .= ' ' . $formattedTagsString;

        preg_match_all('/#\p{L}+([^\s，]*)/u', $content, $matches);

        // 去重并保留原来的顺序
        $unique_tags = array_unique($matches[0]);
        // 将原来的标签和后面跟随的空格一起移除
        $content = preg_replace('/#\p{L}+\s?/u', '', $content);
        //return json_encode($unique_tags);
        // 将去重后的标签添加回去
        $content .= ' ' . implode(' ', $unique_tags);*/

        // 进行 htmlspecialchars 转义
        $content = htmlspecialchars($content, ENT_QUOTES, 'UTF-8');
        $content = nl2br($content);
        // 更新 note 表中的记录
        $update_sql = "UPDATE `note` SET `note_type` = '$note_type', `title` = '$title', `content` = '$content', 
                   `image` = '$image', `tags` = '$tags', `publish_ip` = '$publish_ip', 
                   `check_result` = '$check_result' WHERE `note`.`note_id` = '$nid'";
        //return json_encode($update_sql);
        if ($this->query($update_sql)) {

            $check_query = "SELECT COUNT(*) as count FROM `check_note` WHERE `note_id` = '$nid'";
            $count = json_decode($this->getRow($check_query),true)['count'];
            
            if ($count == 0) {
                // 如果没有记录，则执行插入
                $check_sql = "INSERT INTO `check_note` (`id`, `note_id`, `check_user_id`, `check_type`) VALUES (NULL, '{$nid}', NULL, '1');";
            }else{
                $check_sql = "UPDATE `check_note` SET `check_user_id` = '', `check_type` = '1' WHERE `check_note`.`note_id` = '{$nid}';";
            }
            $this->query($check_sql);



            // 仅当 $image 只包含视频时才操作 video_note 表
            if ($is_video && !$is_image) {
                $check_video_sql = "SELECT COUNT(*) as count FROM `video_note` WHERE `note_id` = '$nid'";
                $result = json_decode($this->getRow($check_video_sql),true);

                // 如果不存在记录，插入新的 video_note 记录
                if ($result['count'] == 0) {
                    $videopic_sql = "INSERT INTO `video_note` (`id`, `note_id`, `video_src`, `is_ok`) 
                                 VALUES (NULL, '$nid', '$image', '0')";
                    $this->query($videopic_sql);
                } else {
                    // 如果存在记录，则更新 video_note 表
                    $videopic_sql = "UPDATE `video_note` SET `video_src` = '$image', `is_ok` = '0' WHERE `note_id` = '$nid'";
                    $this->query($videopic_sql);
                }
            }


            return json_encode(['status'=>200,'message'=>'更新成功！耐心等待笔记审核！']);
        }
        return json_encode(['status'=>201,'message'=>'更新失败！']);
        //$update_sql ="UPDATE `note` SET `note_type` = '0', `title` = '1danvzhu11', `content` = '2 #test1 #test2 #test3 #test41', `image` = '/data/30241004/8d33598e4b811aa89352a5c5.mp4', `tags` = 'test1,test2,test3,test2', `publish_ip` = '1918705307', `check_result` = '0' WHERE `note`.`id` = 3186;"


    }

    public function getVideoNook() {

        $sql = "SELECT * FROM `video_note` WHERE is_ok = '0' limit 100;";
        $res = $this->getAll($sql);
        return $res;
    }

    public function getVideoSrc() {

        $sql = "SELECT n.*,v.*
            FROM video_note v
            JOIN note n ON v.note_id = n.note_id 
            ORDER BY v.id DESC 
            LIMIT 5000";

        $res = $this->getAll($sql);
        return $res;
    }

    public function getAllCheckNotes() {
        // 直接查询 check_note 表、note 表和 user 表的相关信息
        $query = "
        SELECT 
            cn.check_user_id, 
            cn.check_type, 
            n.note_id, 
            n.note_type, 
            n.check_result, 
            n.title, 
            n.content, 
            n.image, 
            n.tags, 
            u.user_id AS note_user_id, 
            u.nickname AS note_nickname, 
            u.avatar AS note_avatar,
            cu.user_id AS check_user_id,
            cu.nickname AS check_nickname, 
            cu.avatar AS check_avatar
        FROM 
            check_note cn
        JOIN 
            note n ON cn.note_id = n.note_id
        JOIN 
            user u ON n.user_id = u.user_id
        LEFT JOIN 
            user cu ON cn.check_user_id = cu.user_id -- 将 check_user_id 与 user 表连接
        LIMIT 5000
    ";

        $result = $this->getAll($query);

        return $result;
    }

    public function getAIformatCheckNotes() {
        $query = "
        SELECT 
            n.note_id, 
            n.title as note_title, 
            n.content as note_content, 
            n.tags as note_keywords
        FROM 
            check_note cn
        JOIN 
            note n ON cn.note_id = n.note_id
        where check_type = 1
        LIMIT 1
    ";
        $result = json_decode($this->getAll($query),true);

        return json_encode($result, JSON_UNESCAPED_UNICODE);;
    }


    public function clearVideoSrc() {

        $sql = "DELETE FROM `video_note` WHERE `is_ok` = 1";

        $res = $this->query($sql);
        return $res;
    }

    public function clearCheckNote() {

        $sql = "DELETE FROM `check_note` WHERE `check_type` = 1";

        $res = $this->query($sql);
        return $res;
    }


    public function VideoChangeok($nid,$isok,$filesrc) {
        if (!isUid($nid)) {
            return json_encode(['status'=>201,'message'=>'note_id error']);
        }
        if ($isok != 1 && $isok != 2) {
            return json_encode(['status'=>201,'message'=>'isok error']);
        }

        $sql = "UPDATE `video_note` SET `is_ok` = '{$isok}' WHERE `note_id` = '{$nid}'";
        if ($isok == 1) {
            $upvideosql = "UPDATE `note` SET `image` = '{$filesrc}' WHERE `note_id` = '{$nid}';";
            $this->query($upvideosql);
        }

        $res = $this->query($sql);
        return $res;
    }


    public function NoteChangeok($nid,$uid,$check_result) {
        if (!isUid($nid)||!isUid($uid)) {
            return json_encode(['status'=>201,'message'=>'n/uid error']);
        }
        if ($check_result != 1 && $check_result != 2) {     //1为通过 2为不通过
            return json_encode(['status'=>201,'message'=>'isok error']);
        }
        $selecsql = "SELECT count(*) as count FROM `check_note` WHERE `note_id` = '{$nid}' and `check_type` = '2'";
        $is_check = json_decode($this->getRow($selecsql),true)['count'];
        if($is_check) {
            return json_encode(['status'=>202,'message'=>'已经审核过了！']);
        }

        $sql1 = "UPDATE `check_note` SET `check_type` = '2',`check_user_id` = '{$uid}' WHERE `note_id` = '{$nid}';";
        $sql2 = "UPDATE `note` SET `check_result` = '{$check_result}' WHERE `note_id` = '{$nid}'";

        $this->query($sql1);
        $this->query($sql2);

        return json_encode(['status'=>200,'message'=>'审核成功！']);
    }

    //nid get notes
    public function getIdNotes($nid) {
        if (!isUid($nid)) {
            return;
        }
        // 查询用户笔记
        // SELECT count(*) FROM `note` WHERE user_id = '310da744a39058d3076ea7eb'
        //  UPDATE `note` SET `user_id`='5bf12f9d0754550001b9cee8' WHERE user_id = '310da744a39058d3076ea7eb'
        $sql = "SELECT n.*, u.nickname, u.avatar FROM `note` n JOIN `user` u ON n.user_id = u.user_id WHERE n.note_id = '{$nid}';";
        $res = $this->getRow($sql);
        return $res;
    }


    public function getIsFans($uid,$fans_id) {
        if (!isUid($fans_id) || !isUid($uid)) {
            return json_encode(['status' => '201', 'message' => '无效uid/fans_id']);
        }
        $sql = "SELECT id FROM fans WHERE user_id = '$uid' AND fans_id = '$fans_id' LIMIT 1";
        $res = $this->getRow($sql);
        if(empty($res)) {
            return false;
        }
        return true;
    }

    // 根据 ID 获取 note 内容的方法
    public function getNoteById($id) {
        $sql = "SELECT note_id,title,note_type,image,user_id FROM note WHERE id = $id";
        return $this->getRow($sql);
    }

    // 动态获取最大记录数的方法
    private function getTotalRecords() {
        $sql = "SELECT COUNT(*) as count FROM note";
        $result = json_decode($this->getRow($sql), true);
        return $result ? (int)$result['count'] : 0;
    }

    public function getMaxRecords() {
        return $this->maxRecords; // 返回最大记录数
    }

    public function getNoteIsLike($noteId,$userId) {
        if (!isUid($userId) || !isUid($noteId)) {
            return;
        }
        // 查询用户是否点赞过这篇笔记
        $likeCheckSql = "SELECT * FROM user_actions 
                     WHERE user_id = '{$userId}' 
                     AND note_id = '{$noteId}' 
                     AND action_type = 0"; // 0代表点赞

        $likeRes = $this->getRow($likeCheckSql);

        // 返回查询结果
        return $likeRes ? true : false;
    }

    public function getLikeAndFav($noteId,$userId) {
        if (!isUid($userId) || !isUid($noteId)) {
            return;
        }
        // 查询用户是否点赞过这篇笔记
        $likeCheckSql = "SELECT * FROM user_actions 
                  WHERE user_id = '{$userId}' 
                  AND note_id = '{$noteId}' 
                  AND action_type IN (0, 1)"; // 0代表点赞，1代表收藏

        $actionDataArray  = json_decode($this->getAll($likeCheckSql),true);
        if (!$actionDataArray){
            return;
        }
        $isLiked = false;
        $isFavorited = false;


        foreach ($actionDataArray as $actionData) {
            if (isset($actionData["action_type"])) {
                if ($actionData["action_type"] === "0") {
                    $isLiked = true; // 如果有任何点赞，设置为 true
                } elseif ($actionData["action_type"] === "1") {
                    $isFavorited = true; // 如果有任何收藏，设置为 true
                }
            }
        }

        $result = [
            "like" => $isLiked,
            "favorite" => $isFavorited,
        ];

        return json_encode($result);
    }

    public function getNoteStatistics($noteId) {
        // 首先尝试查询统计信息
        $sql = "SELECT * FROM note_statistics WHERE note_id = '{$noteId}'";
        $statistics = $this->getRow($sql);

        // 如果没有找到，则插入一条新的记录
        if (!$statistics) {
            $insertSql = "INSERT INTO note_statistics (note_id, likes_count, favorites_count, comments_count) VALUES ('{$noteId}', '0', '0', '0')";
            $this->query($insertSql); // 执行插入操作
            // 重新查询以获取刚插入的记录
            return $this->getRow($sql);
        }

        return $statistics; // 返回查询到的统计信息
    }


    public function getUserInfo($userId) {
        $sql = "SELECT * FROM user WHERE user_id = '{$userId}'";
        $user = $this->getRow($sql);
        return $user;
    }
    
    public function reportNote($nid) {

        if (!isUid($nid)) {
            return json_encode(['status'=>201,'message'=>'nid error']);
        }

        
        $check_query = "SELECT COUNT(*) as count FROM `check_note` WHERE `note_id` = '$nid'";
        $count = json_decode($this->getRow($check_query),true)['count'];
        if ($count == 0) {
            // 如果没有记录，则执行插入
            $check_sql = "INSERT INTO `check_note` (`id`, `note_id`, `check_user_id`, `check_type`) VALUES (NULL, '{$nid}', NULL, '1');";
        }else{
            $check_sql = "UPDATE `check_note` SET `check_user_id` = '', `check_type` = '1' WHERE `check_note`.`note_id` = '{$nid}';";
        }
        $this->query($check_sql);
        
        return json_encode(['status'=>200,'message'=>'举报成功！']);
        

    }
}
?>


